package com.clh.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.clh.common.PageInfo;
import com.clh.dao.FamilyDao;
import com.clh.entity.Family;
import com.clh.entity.FamilyType;
import com.clh.entity.Owner;
import com.clh.util.DBUtil;

/**
 * @Description: TODO(用一句话描述该文件做什么)
 * @author 邱宏宇 
 * @date 2022年5月26日 下午2:50:28
 * @version V1.0
 */
public class FamilyDaoImpl implements FamilyDao{

	@Override
	public List<Family> findFamilyByPage(Family family, int pageNum, int pageSize) {//成员名、身份证、房号查询
		List<Family> list = new ArrayList<Family>();
		StringBuilder sql = new StringBuilder();
		sql.append("select A.family_no,")
		.append(" A.family_name,")
		.append(" A.family_card,")
		.append(" A.family_image,")
		.append(" A.room_no,")
		.append(" A.family_type,")
		.append(" B.familyType_name,")
		.append(" A.family_phone")
		.append(" from family A")
		.append(" inner join family_type B on A.family_type=B.familyType_no");
		//动态sql
		if(true) {
			sql.append(" where 1=1");
		}
		if(family.getFamilyName()!=null && !"".equals(family.getFamilyName())) {
			sql.append(" and A.family_name like "+"'"+family.getFamilyName()+"'");
		}
		if(family.getFamilyCard()!=null && !"".equals(family.getFamilyCard())) {
			sql.append(" and A.family_card like "+ "'"+family.getFamilyCard()+"'");
		}
		if(family.getRoomNo()!=null && !"".equals(family.getRoomNo())) {
			sql.append(" and A.room_no like "+"'"+family.getRoomNo()+"'");
		}
		//分页功能
		sql.append(" limit ").append((pageNum-1)*pageSize).append(",")
		.append(pageSize);
		
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while(rs.next()) {
				//获取结果集
				Integer familyNo = rs.getInt("family_no");
				String familyName = rs.getString("family_name");
				String familyCard = rs.getString("family_card");
				String familyImage = rs.getString("family_image");
				String roomNo= rs.getString("room_no");
				String familyTypeName = rs.getString("familyType_name");
				String familyPhone = rs.getString("family_phone");
				
				Family f = new Family();
				f.setFamilyNo(familyNo);
				f.setFamilyName(familyName);
				f.setFamilyCard(familyCard);
				f.setFamilyImage(familyImage);
				f.setFamilyPhone(familyPhone); 
				
				Owner o = new Owner();
				o.setRoomNo(roomNo);
				FamilyType fT = new FamilyType();
				fT.setFamilyTypeName(familyTypeName);
				f.setOwner(o);
				f.setFamilyType(fT);
				list.add(f);		
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int countByCondition(Family family) {
		StringBuilder sql = new StringBuilder();
		int n = 0;
		sql.append("select count(*) from family");
		//动态sql
		sql.append(" where 1=1");
		//条件
		if(family.getFamilyName()!=null && !"".equals(family.getFamilyName())) {
			sql.append(" and family_name like "+"'"+family.getFamilyName()+"'");
		}
		if(family.getFamilyCard()!=null && !"".equals(family.getFamilyCard())) {
			sql.append(" and family_card like "+ "'"+family.getFamilyCard()+"'");
		}
		if(family.getRoomNo()!=null && !"".equals(family.getRoomNo())) {
			sql.append(" and room_no like "+"'"+family.getRoomNo()+"'");
		}
		
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			if(rs.next()) {
				n=rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		return n;
	}

	@Override
	public boolean addFamily(Family family) {
		StringBuilder sql = new StringBuilder();
		sql.append("insert into family (family_name,family_card,family_image,room_no,family_type,family_phone)")
		.append(" values (?,?,?,?,?,?)");
		//参数信息
		String familyName = family.getFamilyName();
		String familyCard = family.getFamilyCard();
		String familyImage = family.getFamilyImage();
		String roomNo = family.getRoomNo();
		int familyTypeId = family.getFamilyTypeId();
		String familyPhone = family.getFamilyPhone();
		System.out.println("familyDaoimpl:familyImage"+familyImage);
		int n = DBUtil.update(sql.toString(),familyName,familyCard,familyImage,roomNo,familyTypeId,familyPhone);
		if(n>0) {
			return true;
		}
		return false;
	}

	@Override
	public boolean delFamily(String familyCard) {
		StringBuilder sql = new StringBuilder();
		sql.append("delete from family where family_card=?");
		
		int n = DBUtil.update(sql.toString(), familyCard);
		if(n>0) {
			return true;
		}
		return false;
	}

	@Override
	public boolean modifyFamily(Family family) {
		StringBuilder sql = new StringBuilder();
		sql.append("update family set family_name=?,");
		//若有传递图片时拼接
		if(family.getFamilyImage()!=null) {
			sql.append("family_image=?,");
		}
		
		sql.append("room_no=?,")
		.append("family_type=?,")
		.append("family_phone=?")
		.append(" where family_card=?");
		int n = 0;
		if(family.getFamilyImage()!=null) {
			n = DBUtil.update(sql.toString(), family.getFamilyName(),family.getFamilyImage(),family.getRoomNo(),
				family.getFamilyTypeId(),family.getFamilyPhone(),family.getFamilyCard());
		}else {
			n = DBUtil.update(sql.toString(), family.getFamilyName(),family.getRoomNo(),
					family.getFamilyTypeId(),family.getFamilyPhone(),family.getFamilyCard());
		}
		
		
		if(n>0) {
			return true;
		}
		return false;
	}

	@Override
	public Family getByFamilyCard(String familyCard) {
		StringBuilder sql= new StringBuilder();
		sql.append("select * from family where family_card=?");
		
		ResultSet rs = DBUtil.query(sql.toString(), familyCard);
		try {
			while(rs.next()) {
				//构造family对象
				Family family = new Family();
				family.setFamilyName(rs.getString("family_name"));
				family.setFamilyCard(familyCard);
				family.setFamilyImage(rs.getString("family_image"));
				family.setRoomNo(rs.getString("room_no"));
				family.setFamilyTypeId(rs.getInt("family_type"));
				family.setFamilyPhone(rs.getString("family_phone"));
				
				return family;
			}
		} catch (Exception e) {
			e.printStackTrace(); 
		}
		
		return null;
	}

	@Override
	public List<Family> getListByRoomNo(String roomNo) {
		List<Family> list = new ArrayList<Family>();
		StringBuilder sql = new StringBuilder();
		sql.append("select A.family_name,")
		.append(" A.family_card,")
		.append(" A.family_image,")
		.append(" A.room_no,")
		.append(" A.family_type,")
		.append(" B.familyType_name,")
		.append(" A.family_phone")
		.append(" from family A inner join family_type B")
		.append(" on A.family_type=B.familyType_no")
		.append(" where A.room_no = ?");
		
		ResultSet rs = DBUtil.query(sql.toString(), roomNo);
		try {
			while(rs.next()) {
				Family family = new Family();
				family.setFamilyName(rs.getString("family_name"));
				family.setFamilyCard(rs.getString("family_card"));
				family.setFamilyImage(rs.getString("family_image"));
				family.setRoomNo(roomNo);
				FamilyType familyType = new FamilyType();
				familyType.setFamilyTypeNo(rs.getInt("family_type"));
				familyType.setFamilyTypeName(rs.getString("familyType_name"));
				family.setFamilyType(familyType);
				family.setFamilyPhone(rs.getString("family_phone"));
				
				list.add(family);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	
}
